package com.orangelife.webservice.service.statistics.impl;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.core.Response;

import org.springframework.stereotype.Service;

import com.github.pagehelper.Page;
import com.orangelife.common.AbstractV2TransferService;
import com.orangelife.framework.toolkit.validate.ValidateUtil;
import com.orangelife.redis.core.redis.session.redisSession.WebServiceSession;
import com.orangelife.service.PropFeeType.IPropFeeTypeService;
import com.orangelife.service.entiy.RateTable;
import com.orangelife.service.outexcell.CreateExcellByPoi;
import com.orangelife.service.statistics.IStatisticsService;
import com.orangelife.setting.constant.CodeConst;
import com.orangelife.setting.constant.InfoConst;
import com.orangelife.utils.PropertyUtils;
import com.orangelife.utils.StringUtils;
import com.orangelife.webservice.service.statistics.IStatisticsWebService;
import com.orangelife.webservice.service.statistics.entity.StatisticsDto;

@Service("statisticsWebService")
public class StatisticsWebServiceImpl extends AbstractV2TransferService
		implements IStatisticsWebService {

	@Resource(name = "statisticsService")
	private IStatisticsService statisticsService;
	@Resource(name = "propFeeTypeService")
	private IPropFeeTypeService propFeeTypeService;

	@Override
	public Response statisticFeeDetail(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {
		if (st.getCommIDs() == null || st.getCommIDs().length == 0) {

			return this.abnormalResp(100002, "小区信息不能为空");

		}

		Thread.sleep(70000);
		System.out.println(12);
		List<Map<String, Object>> l = statisticsService.getFeeStatistics(st);

		// TODO Auto-generated method stub
		return this.normalResp(0, 1, 0l, l);

	}

	@Override
	public Response statisticFeeDetailBycomm(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {
		if (st.getCommIDs() == null || st.getCommIDs().length == 0) {

			return this.abnormalResp(100002, "小区信息不能为空");

		}

		List<Map<String, Object>> l = statisticsService.getFeeStatistics(st);

		// TODO Auto-generated method stub
		return this.normalResp(0, 1, 0l, l);
	}

	@Override
	public Response payRateByDate(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {
		if (st.getCommIDs() == null || st.getCommIDs().length == 0) {

			return this.abnormalResp(100002, "小区信息不能为空");

		}

		List<Map<String, Object>> l = statisticsService.getFeeRateByDate(st);

		// TODO Auto-generated method stub
		return this.normalResp(0, 1, 0l, l);
	}

	@Override
	public Response ownerBillInfo(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {

		Map<String, Object> m = statisticsService.getOwnerBillInfo(st);

		// TODO Auto-generated method stub
		return this.normalResp(m);

	}

	@Override
	public Response paybyMon(HttpServletRequest servletRequest, StatisticsDto st)
			throws Exception {
		// TODO Auto-generated method stub

		List<Map<String, Object>> l = statisticsService.paybyMon(st);

		Long total = ((Page<Map<String, Object>>) l).getTotal();
		return this.normalResp(st.getPageIndex(), st.getPageSize(), total, l);
	}

	@Override
	public Response paybyMonAll(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {
		Map<String, Object> l = statisticsService.paybyMonAll(st);
		return this.normalResp(l);
	}

	@Override
	public Response getCommOweInfo(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {

		List<Map<String, Object>> l = statisticsService.getCommOweInfo(st, "1");
		return this.normalResp(0, 1, 0l, l);
	}

	@Override
	public Response getCommOweInfoAll(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {

		Map<String, Object> l = statisticsService.getCommOweInfoAll(st, null);
		return this.normalResp(l);
	}

	@Override
	public Response getOwerOweFeeInfo(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {

		if (StringUtils.isBlank(st.getIsall())) {

			st.setIsall("1");

		}

		List<Map<String, Object>> l = statisticsService.getOwerOweFeeInfo(st,
				st.getIsall());

		Long total = ((Page<Map<String, Object>>) l).getTotal();

		return this.normalResp(st.getPageIndex(), st.getPageSize(), total, l);
	}

	@Override
	public Response getOwerOweFeeInfoAll(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {
		Map<String, Object> l = statisticsService
				.getOwerOweFeeInfoAll(st, null);

		return this.normalResp(l);
	}

	@Override
	public Response getFeeTypeCode(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {

		// TODO Auto-generated method stub
		return this.normalResp(0, 1, 0l, statisticsService.getFeeTypeCode(st));
	}

	@Override
	public Response getCommOweInfoDetail(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {
		// TODO Auto-generated method stub
		return this.normalResp(0, 1, 0l,
				statisticsService.getCommOweInfoDetail(st));
	}

	@SuppressWarnings("unchecked")
	@Override
	public Response downLoadBillingSchedule(HttpServletRequest servletRequest,
			HttpServletResponse servletResponse, StatisticsDto st)
			throws Exception {

		String tk = servletRequest.getParameter("tk");
		WebServiceSession webServiceSession = this.getWebServiceSession(tk);
		if (webServiceSession == null) {
			return this.abnormalResp(CodeConst.SYS_CODE_2001,
					InfoConst.SYS_INFO_2001);

		}
		List<Map<String, Object>> valuesDbase = statisticsService
				.paybillDetail(st);
		Map<String, Object> TotalAndTitlesDbase = statisticsService
				.paybyMonAll(st);
		List<String> titles = new ArrayList<String>();
		List<String> keys = new ArrayList<String>();
		List<List<String>> values = new ArrayList<List<String>>();
		List<String> totals = new ArrayList<String>();
		List<Map<String, Object>> tempheaders = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> temptotals = new ArrayList<Map<String, Object>>();
		try {
			tempheaders = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("header");
			temptotals = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("list");

		} catch (Exception e) {
			// TODO: handle exception
		}

		titles.add("序号");
		titles.add("开单时间");
		titles.add("发票单号");
		titles.add("产权类型");
		titles.add("户号");
		titles.add("业主姓名");
		Map<String, Object> map2 = temptotals.get(0);
		for (Map<String, Object> map1 : tempheaders) {
			String header = (String) map1.get("name");
			String key = (String) map1.get("code");
			BigDecimal total = null;
			if (map2.get("XM" + key) != null) {

				total = (BigDecimal) map2.get("XM" + key);
			} else {
				total = BigDecimal.ZERO;
			}

			titles.add(header);
			totals.add(total.toString());
			keys.add(key);

		}
		titles.add("合计（元）");
		// =====================获取统计时间
		String stt = st.getStartTime();
		String end = st.getEndTime();

		if (StringUtils.isBlank(stt)) {

			stt = StringUtils.toString(map2.get("mindate"), "");

		}

		if (StringUtils.isBlank(end)) {

			end = StringUtils.toString(map2.get("maxdate"), "");

		}

		String time = stt.replace("-", ".") + "-" + end.replace("-", ".");

		// =====================获取统计时间
		BigDecimal totaltotal = (BigDecimal) map2.get("totalAmount");
		totals.add("" + totaltotal);

		int k = 1;
		for (Map<String, Object> map : valuesDbase) {

			List<String> rowValue = new ArrayList<String>();

			rowValue.add("" + k);
			k++;

			String paytime = (String) map.get("pay_time");
			String apartmentType = (String) map.get("apartmentType");
			String invoice_num = (String) map.get("invoice_num");
			String building_name = (String) map.get("building_name");
			String unit_name = (String) map.get("unit_name");
			String apartment_num = (String) map.get("apartment_num");
			String ownerName = (String) map.get("ownerName");

			rowValue.add(paytime);
			rowValue.add(invoice_num);
			rowValue.add(apartmentType);
			rowValue.add(building_name + "_" + unit_name + "_" + apartment_num);
			rowValue.add(ownerName);
			for (int i = 0; i < keys.size(); i++) {

				BigDecimal value = (BigDecimal) map.get("XM" + (keys.get(i)));

				rowValue.add(value.toString());

			}

			BigDecimal pay_money = (BigDecimal) map.get("pay_money");
			rowValue.add(pay_money.toString());

			values.add(rowValue);
		}

		String fileName = this.processFileName(servletRequest, "开单明细表");
		servletResponse.setContentType("application/vnd.ms-excel");
		servletResponse.setHeader("Content-disposition", "attachment;filename="
				+ fileName + ".xls");
		OutputStream ouputStream = servletResponse.getOutputStream();
		String path = servletRequest.getSession().getServletContext()
				.getRealPath("excel");
		CreateExcellByPoi.outBillingSchedule(titles, values, totals,
				StringUtils.toString(map2.get("comm")), time,
				StringUtils.toString(webServiceSession.getRealname(), ""), path
						+ "/开单明细表.xls", ouputStream, 6, 12);

		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Response downTuitionTablesBySingleCell(
			HttpServletRequest servletRequest,
			HttpServletResponse servletResponse, StatisticsDto st)
			throws Exception {

		String tk = servletRequest.getParameter("tk");
		WebServiceSession webServiceSession = this.getWebServiceSession(tk);
		if (webServiceSession == null) {
			return this.abnormalResp(CodeConst.SYS_CODE_2001,
					InfoConst.SYS_INFO_2001);

		}

		List<Map<String, Object>> valuesDbase = statisticsService
				.getOwerOweFeeInfo(st, "1");
		Map<String, Object> TotalAndTitlesDbase = statisticsService
				.getOwerOweFeeInfoAll(st, null);

		List<String> titles = new ArrayList<String>();
		List<String> keys = new ArrayList<String>();
		List<List<String>> values = new ArrayList<List<String>>();
		List<String> totals = new ArrayList<String>();
		List<Map<String, Object>> tempheaders = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> temptotals = new ArrayList<Map<String, Object>>();
		try {
			tempheaders = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("header");
			temptotals = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("list");

		} catch (Exception e) {
			// TODO: handle exception
		}
		Map<String, Object> map2 = temptotals.get(0);
		titles.add("序号");
		titles.add("产权类型");
		titles.add("户号");
		titles.add("业主姓名");
		titles.add("计费面积");

		// =====================获取统计时间
		String stt = st.getStartTime();
		String end = st.getEndTime();

		if (StringUtils.isBlank(stt)) {

			stt = StringUtils.toString(map2.get("mindate"), "");

		}

		if (StringUtils.isBlank(end)) {

			end = StringUtils.toString(map2.get("maxdate"), "");

		}

		String time = stt.replace("-", ".") + "-" + end.replace("-", ".");

		// =====================获取统计时间
		for (Map<String, Object> map1 : tempheaders) {
			String header = (String) map1.get("name");
			String key = (String) map1.get("code");

			BigDecimal total = null;
			if (map2.get("XM" + key) != null) {

				total = (BigDecimal) map2.get("XM" + key);
			} else {
				total = BigDecimal.ZERO;
			}

			titles.add(header);
			totals.add("" + total);
			keys.add(key);

		}
		titles.add("合计（元）");

		BigDecimal totaltotal = (BigDecimal) map2.get("totalAmount");
		totals.add("" + totaltotal);

		int k = 1;
		for (Map<String, Object> map : valuesDbase) {

			List<String> rowValue = new ArrayList<String>();

			rowValue.add("" + k);
			k++;
			String temp = (String) map.get("apartmentType");
			String temp1 = (String) map.get("building_name");
			String temp2 = (String) map.get("unit_name");
			String temp3 = (String) map.get("apartment_num");
			String temp4 = (String) map.get("ownerName");
			String temp5 = StringUtils.toString(map.get("fee_area"), "");
			rowValue.add(temp);
			rowValue.add(temp1 + "_" + temp2 + "_" + temp3);
			rowValue.add(temp4);
			rowValue.add(temp5);

			for (int i = 0; i < keys.size(); i++) {

				BigDecimal value = (BigDecimal) map.get("XM" + keys.get(i));

				rowValue.add(value.toString());

			}

			BigDecimal totalvalue = (BigDecimal) map.get("totalAmount");
			rowValue.add(totalvalue.toString());

			values.add(rowValue);
		}
		String fileName = this.processFileName(servletRequest,
				"欠费统计表(单小区明细-按筛选条件导出)");
		servletResponse.setContentType("application/vnd.ms-excel");
		servletResponse.setHeader("Content-disposition", "attachment;filename="
				+ fileName + ".xls");
		OutputStream ouputStream = servletResponse.getOutputStream();

		String path = servletRequest.getSession().getServletContext()
				.getRealPath("excel");
		System.out.println(path);
		CreateExcellByPoi.outTuitionTablesBySingleCell(titles, values, totals,
				StringUtils.toString(map2.get("comm")), time,
				StringUtils.toString(webServiceSession.getRealname(), ""), path
						+ "/欠费统计表(单小区明细-按筛选条件导出)1.xls", ouputStream, 5,
				keys.size() + 6);

		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Response downTuitionTablesByCells(HttpServletRequest servletRequest,
			HttpServletResponse servletResponse, StatisticsDto st)
			throws Exception {

		String tk = servletRequest.getParameter("tk");
		WebServiceSession webServiceSession = this.getWebServiceSession(tk);
		if (webServiceSession == null) {
			// return this.abnormalResp(CodeConst.SYS_CODE_2001,
			// InfoConst.SYS_INFO_2001);

		}

		String time = st.getStartTime().replace("-", ".") + "-";

		Map<String, Object> TotalAndTitlesDbase = statisticsService
				.getCommOweInfoAll(st, null);
		List<Map<String, Object>> valueDbase = statisticsService
				.getCommOweInfo(st, "1");
		List<String> titles = new ArrayList<String>();
		List<String> keys = new ArrayList<String>();
		List<List<String>> values = new ArrayList<List<String>>();
		Map<String, BigDecimal> tt = new HashMap<String, BigDecimal>();
		List<String> totals = new ArrayList<String>();
		List<Map<String, Object>> tempheaders = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> tempValues = new ArrayList<Map<String, Object>>();
		try {
			tempheaders = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("header");
			tempValues = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("list");

		} catch (Exception e) {
			// TODO: handle exception
		}
		titles.add("序号");
		titles.add("小区名称");

		Map<String, Object> map2 = tempValues.get(0);
		for (Map<String, Object> map1 : tempheaders) {
			String header = (String) map1.get("name");
			String key = (String) map1.get("code");
			BigDecimal total = null;
			if (map2.get("XM" + key) != null) {

				total = (BigDecimal) map2.get("XM" + key);
			} else {
				total = BigDecimal.ZERO;
			}
			totals.add("" + total);
			titles.add(header);
			keys.add(key);

		}

		// =====================获取统计时间
		String stt = st.getStartTime();
		String end = st.getEndTime();

		if (StringUtils.isBlank(stt)) {

			stt = StringUtils.toString(map2.get("mindate"), "");

		}

		if (StringUtils.isBlank(end)) {

			end = StringUtils.toString(map2.get("maxdate"), "");

		}

		// =====================获取统计时间

		titles.add("合计（元）");

		int k = 1;
		for (Map<String, Object> map : valueDbase) {

			List<String> rowValue = new ArrayList<String>();

			rowValue.add("" + k);
			k++;

			String comm = (String) map.get("comm");
			rowValue.add(comm);

			for (int j = 0; j < keys.size(); j++) {

				tt.put("XM" + keys.get(j), BigDecimal.ZERO);

			}
			for (int i = 0; i < keys.size(); i++) {
				BigDecimal value = new BigDecimal(StringUtils.toString(
						map.get("XM" + keys.get(i)), "0"));

				BigDecimal sum = tt.get("XM" + keys.get(i));
				sum = sum.add(value);
				tt.put("XM" + keys.get(i), sum);
				rowValue.add(value.toString());

			}

			BigDecimal totalvalue = new BigDecimal(StringUtils.toString(
					map.get("totalAmount"), "0"));
			rowValue.add(totalvalue.toString());

			values.add(rowValue);
		}

		totals.add(map2.get("totalAmount").toString());

		String fileName = this.processFileName(servletRequest, "欠费统计表（多个小区总计）");
		servletResponse.setContentType("application/vnd.ms-excel");
		servletResponse.setHeader("Content-disposition", "attachment;filename="
				+ fileName + ".xls");
		OutputStream ouputStream = servletResponse.getOutputStream();

		String path = servletRequest.getSession().getServletContext()
				.getRealPath("excel");
		System.out.println(path);
		CreateExcellByPoi.outTuitionTablesByCells(titles, values, totals, null,
				time,
				StringUtils.toString(webServiceSession.getRealname(), ""), path
						+ "/欠费统计表（多个小区总计）1.xls", ouputStream, 2,
				keys.size() + 3);
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Response downLoadScheduleOfFees(HttpServletRequest servletRequest,
			HttpServletResponse servletResponse, StatisticsDto st)
			throws Exception {
		String tk = servletRequest.getParameter("tk");
		WebServiceSession webServiceSession = this.getWebServiceSession(tk);
		if (webServiceSession == null) {
			// return this.abnormalResp(CodeConst.SYS_CODE_2001,
			// InfoConst.SYS_INFO_2001);

		}

		List<Map<String, Object>> valuesDbase = statisticsService.paybyMon(st);
		Map<String, Object> TotalAndTitlesDbase = statisticsService
				.paybyMonAll(st);
		List<String> titles = new ArrayList<String>();
		List<String> keys = new ArrayList<String>();
		List<List<String>> values = new ArrayList<List<String>>();
		List<String> totals = new ArrayList<String>();
		List<Map<String, Object>> tempheaders = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> temptotals = new ArrayList<Map<String, Object>>();
		try {
			tempheaders = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("header");
			temptotals = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("list");

		} catch (Exception e) {
			// TODO: handle exception
		}

		titles.add("序号");
		titles.add("缴费月份");
		Map<String, Object> map2 = temptotals.get(0);
		for (Map<String, Object> map1 : tempheaders) {
			String header = (String) map1.get("name");
			String key = (String) map1.get("code");
			BigDecimal total = null;
			if (map2.get("XM" + key) != null) {

				total = (BigDecimal) map2.get("XM" + key);
			} else {
				total = BigDecimal.ZERO;
			}

			titles.add(header);
			totals.add(total.toString());
			keys.add(key);

		}
		titles.add("合计（元）");
		// =====================获取统计时间
		String stt = st.getStartTime();
		String end = st.getEndTime();

		if (StringUtils.isBlank(stt)) {

			stt = StringUtils.toString(map2.get("mindate"), "");

		}

		if (StringUtils.isBlank(end)) {

			end = StringUtils.toString(map2.get("maxdate"), "");

		}

		String time = stt.replace("-", ".") + "-" + end.replace("-", ".");

		// =====================获取统计时间
		BigDecimal totaltotal = (BigDecimal) map2.get("totalAmount");
		totals.add("" + totaltotal);

		int k = 1;
		for (Map<String, Object> map : valuesDbase) {

			List<String> rowValue = new ArrayList<String>();

			rowValue.add("" + k);
			k++;
			String month = (String) map.get("paytime");
			rowValue.add(month);
			for (int i = 0; i < keys.size(); i++) {

				BigDecimal value = (BigDecimal) map.get("XM" + keys.get(i));

				rowValue.add(value.toString());

			}

			BigDecimal totalvalue = (BigDecimal) map.get("totalAmount");
			rowValue.add(totalvalue.toString());

			values.add(rowValue);
		}

		servletResponse
				.setContentType("application/vnd.ms-excel;charset=UTF-8");

		String fileName = this.processFileName(servletRequest, "缴费明细表");
		servletResponse.setHeader("Content-disposition", "attachment;filename="
				+ fileName + ".xls");
		OutputStream ouputStream = servletResponse.getOutputStream();
		String path = servletRequest.getSession().getServletContext()
				.getRealPath("excel");
		CreateExcellByPoi.outScheduleOfFees(titles, values, totals,
				StringUtils.toString(map2.get("comm"), ""), time,
				StringUtils.toString(webServiceSession.getRealname(), ""), path
						+ "/缴费明细表.xls", ouputStream, keys.size() + 3);

		return null;
	}

	@Override
	public Response downLoadRateTablesSingleCell(
			HttpServletRequest servletRequest,
			HttpServletResponse servletResponse, StatisticsDto st)
			throws Exception {
		String tk = servletRequest.getParameter("tk");
		WebServiceSession webServiceSession = this.getWebServiceSession(tk);
		if (webServiceSession == null) {
			return this.abnormalResp(CodeConst.SYS_CODE_2001,
					InfoConst.SYS_INFO_2001);

		}

		st.setIsall("1");
		List<Map<String, Object>> valuesDbase = statisticsService
				.getCommOweInfoDetail(st);

		List<String> titles = new ArrayList<String>();
		List<List<String>> values = new ArrayList<List<String>>();
		List<String> totals = new ArrayList<String>();

		titles.add("序号");
		titles.add("收费类目");
		titles.add("应收金额（元）");
		titles.add("已收金额（元）");
		titles.add("未收金额（元）");
		titles.add("收费率");

		int k = 1;
		RateTable r = new RateTable("总计", BigDecimal.ZERO, BigDecimal.ZERO,
				BigDecimal.ZERO, BigDecimal.ZERO);

		String mindate = "";
		String maxdate = "";
		String comm = "";
		for (Map<String, Object> map : valuesDbase) {
			List<String> row = new ArrayList<String>();
			row.add("" + k);
			k++;
			if (StringUtils.isBlank(mindate)) {

				mindate = map.get("mindate").toString();
			} else {

				if (mindate.compareTo(map.get("mindate").toString()) > 0) {

					mindate = map.get("mindate").toString();

				}

			}
			if (StringUtils.isBlank(maxdate)) {

				maxdate = map.get("maxdate").toString();
			}
			{

				if (maxdate.compareTo(map.get("maxdate").toString()) < 0) {

					maxdate = map.get("maxdate").toString();

				}

			}
			if (StringUtils.isBlank(comm)) {

				comm = map.get("comm").toString();
			}
			String name = (String) map.get("name");

			BigDecimal payAmount = (BigDecimal) map.get("payAmount");
			BigDecimal oweAmount = (BigDecimal) map.get("oweAmount");
			BigDecimal totalAmount = (BigDecimal) map.get("totalAmount");

			BigDecimal rate = payAmount.divide(totalAmount, 2,
					BigDecimal.ROUND_HALF_UP);
			row.add(name);
			row.add(totalAmount.toString());
			row.add(payAmount.toString());
			row.add(oweAmount.toString());
			row.add("%" + rate.multiply(BigDecimal.valueOf(100)).toString());

			r.setPayAmount(r.getPayAmount().add(payAmount));
			r.setOweAmount(r.getOweAmount().add(oweAmount));
			r.setTotalAmount(r.getTotalAmount().add(totalAmount));

			values.add(row);

		}

		// =====================获取统计时间
		String stt = st.getStartTime();
		String end = st.getEndTime();

		if (StringUtils.isBlank(stt)) {

			stt = mindate;

		}

		if (StringUtils.isBlank(end)) {

			end = maxdate;

		}
		//

		String time = stt.replace("-", ".") + "-" + end.replace("-", ".");

		// =====================获取统计时间

		totals.add(r.getTotalAmount().toString());
		totals.add(r.getPayAmount().toString());
		totals.add(r.getOweAmount().toString());
		BigDecimal rate = r.getPayAmount().divide(r.getTotalAmount(), 2,
				BigDecimal.ROUND_HALF_UP);
		totals.add("%" + rate.multiply(BigDecimal.valueOf(100)).toString());

		String fileName = this.processFileName(servletRequest, "收费率统计表（单小区）");
		servletResponse.setContentType("application/vnd.ms-excel");
		servletResponse.setHeader("Content-disposition", "attachment;filename="
				+ fileName + ".xls");
		OutputStream ouputStream = servletResponse.getOutputStream();

		String path = servletRequest.getSession().getServletContext()
				.getRealPath("excel");
		System.out.println(path);
		CreateExcellByPoi.outRateTablesSingleCell(titles, values, totals, comm,
				time,
				StringUtils.toString(webServiceSession.getRealname(), ""), path
						+ "/收费率统计表（单小区）1.xls", ouputStream, 2, 6);

		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Response downLoadRateTableCells(HttpServletRequest servletRequest,
			HttpServletResponse servletResponse, StatisticsDto st)
			throws Exception {

		String tk = servletRequest.getParameter("tk");
		WebServiceSession webServiceSession = this.getWebServiceSession(tk);
		if (webServiceSession == null) {
			return this.abnormalResp(CodeConst.SYS_CODE_2001,
					InfoConst.SYS_INFO_2001);

		}

		Map<String, Object> TotalAndTitlesDbase = statisticsService
				.getCommOweInfoAll(st, null);
		List<Map<String, Object>> valueDbase = statisticsService
				.getCommOweInfo(st, "1");
		List<String> titles = new ArrayList<String>();
		List<List<String>> values = new ArrayList<List<String>>();
		List<String> totals = new ArrayList<String>();
		List<Map<String, Object>> tempValues = new ArrayList<Map<String, Object>>();
		try {

			tempValues = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("list");

		} catch (Exception e) {
			// TODO: handle exception
		}

		titles.add("小区名称");
		titles.add("应收金额（元）");
		titles.add("已收金额（元）");
		titles.add("未收金额（元）");
		titles.add("收费率");

		for (Map<String, Object> map : valueDbase) {

			List<String> rowValue = new ArrayList<String>();
			String comm = (String) map.get("comm");
			rowValue.add(comm);
			BigDecimal payAmount = (BigDecimal) map.get("payAmount");
			BigDecimal oweAmount = (BigDecimal) map.get("oweAmount");
			BigDecimal totalAmount = (BigDecimal) map.get("totalAmount");
			BigDecimal rate = payAmount.multiply(BigDecimal.valueOf(100))
					.divide(totalAmount, 2, BigDecimal.ROUND_HALF_UP);

			rowValue.add(totalAmount.toString());
			rowValue.add(payAmount.toString());
			rowValue.add(oweAmount.toString());
			rowValue.add(rate.toString() + "%");

			values.add(rowValue);
		}
		Map<String, Object> map2 = tempValues.get(0);
		BigDecimal payAmount = (BigDecimal) map2.get("payAmount");
		BigDecimal oweAmount = (BigDecimal) map2.get("oweAmount");
		BigDecimal totalAmount = (BigDecimal) map2.get("totalAmount");
		BigDecimal rate = payAmount.multiply(BigDecimal.valueOf(100)).divide(
				totalAmount, 2, BigDecimal.ROUND_HALF_UP);

		totals.add(totalAmount.toString());
		totals.add(payAmount.toString());
		totals.add(oweAmount.toString());
		totals.add(rate.toString() + "%");

		// =====================获取统计时间
		String stt = st.getStartTime();
		String end = st.getEndTime();

		if (StringUtils.isBlank(stt)) {

			stt = StringUtils.toString(map2.get("mindate"), "");

		}

		if (StringUtils.isBlank(end)) {

			end = StringUtils.toString(map2.get("maxdate"), "");

		}

		String time = stt.replace("-", ".") + "-" + end.replace("-", ".");

		// =====================获取统计时间

		String fileName = this.processFileName(servletRequest, "收费率统计表（多个小区）");
		servletResponse.setContentType("application/vnd.ms-excel");
		servletResponse.setHeader("Content-disposition", "attachment;filename="
				+ fileName + ".xls");
		OutputStream ouputStream = servletResponse.getOutputStream();

		String path = servletRequest.getSession().getServletContext()
				.getRealPath("excel");
		System.out.println(path);
		CreateExcellByPoi.outRateTablesCells(titles, values, totals, null,
				time,
				StringUtils.toString(webServiceSession.getRealname(), ""), path
						+ "/收费率统计表（多个小区）1.xls", ouputStream, 1, 5);
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Response downLoadRateTableSingleCellByCategory(
			HttpServletRequest servletRequest,
			HttpServletResponse servletResponse, StatisticsDto st)
			throws Exception {
		String tk = servletRequest.getParameter("tk");
		WebServiceSession webServiceSession = this.getWebServiceSession(tk);
		if (webServiceSession == null) {
			return this.abnormalResp(CodeConst.SYS_CODE_2001,
					InfoConst.SYS_INFO_2001);

		}
		Map<String, Object> TotalAndTitlesDbase = statisticsService
				.getCommOweInfoAll(st, null);
		List<Map<String, Object>> valuesDbase = statisticsService
				.getCommOweInfoDetail(st);

		List<String> titles = new ArrayList<String>();

		List<String> totals = new ArrayList<String>();
		Map<String, List<Map<String, Object>>> tongge = new HashMap<String, List<Map<String, Object>>>();
		List<Map<String, Object>> tempValues = new ArrayList<Map<String, Object>>();
		try {

			tempValues = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("list");

		} catch (Exception e) {
			// TODO: handle exception
		}
		titles.add("收费类目");
		titles.add("月份");
		titles.add("应收金额（元）");
		titles.add("已收金额（元）");
		titles.add("未收金额（元）");
		titles.add("收费率");

		for (Map<String, Object> map : valuesDbase) {

			String code = (String) map.get("code");
			if (tongge.get(code) == null) {
				List<Map<String, Object>> tempp = new ArrayList<Map<String, Object>>();
				tempp.add(map);
				tongge.put(code, tempp);
			} else {
				tongge.get(code).add(map);
			}

		}

		Map<String, Object> totalmap = tempValues.get(0);
		BigDecimal payAmount = (BigDecimal) totalmap.get("payAmount");
		BigDecimal oweAmount = (BigDecimal) totalmap.get("oweAmount");
		BigDecimal totalAmount = (BigDecimal) totalmap.get("totalAmount");
		BigDecimal rate = payAmount.divide(totalAmount, 2,
				BigDecimal.ROUND_HALF_UP);
		totals.add(totalAmount.toString());
		totals.add(payAmount.toString());
		totals.add(oweAmount.toString());
		totals.add("%" + rate.multiply(BigDecimal.valueOf(100)).toString());
		// =====================获取统计时间
		String stt = st.getStartTime();
		String end = st.getEndTime();

		if (StringUtils.isBlank(stt)) {

			stt = StringUtils.toString(totalmap.get("mindate"), "");

		}

		if (StringUtils.isBlank(end)) {

			end = StringUtils.toString(totalmap.get("maxdate"), "");

		}

		String time = stt.replace("-", ".") + "-" + end.replace("-", ".");

		// =====================获取统计时间
		String fileName = this.processFileName(servletRequest,
				"收费率统计表（单小区-按类目划分）");
		servletResponse.setContentType("application/vnd.ms-excel");
		servletResponse.setHeader("Content-disposition", "attachment;filename="
				+ fileName + ".xls");
		OutputStream ouputStream = servletResponse.getOutputStream();

		String path = servletRequest.getSession().getServletContext()
				.getRealPath("excel");
		System.out.println(path);
		CreateExcellByPoi.outRateTablesSingleCellByCategory(titles, tongge,
				totals, StringUtils.toString(totalmap.get("comm"), ""), time,
				StringUtils.toString(webServiceSession.getRealname(), ""), path
						+ "/收费率统计表（单小区-按类目划分）1.xls", ouputStream, 2, 6);

		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Response downLoadRateTableSingleCellByMonth(
			HttpServletRequest servletRequest,
			HttpServletResponse servletResponse, StatisticsDto st)
			throws Exception {
		String tk = servletRequest.getParameter("tk");
		WebServiceSession webServiceSession = this.getWebServiceSession(tk);
		if (webServiceSession == null) {
			return this.abnormalResp(CodeConst.SYS_CODE_2001,
					InfoConst.SYS_INFO_2001);

		}
		Map<String, Object> TotalAndTitlesDbase = statisticsService
				.getCommOweInfoAll(st, null);
		List<Map<String, Object>> valuesDbase = statisticsService
				.getCommOweInfoDetail(st);

		List<String> titles = new ArrayList<String>();

		List<String> totals = new ArrayList<String>();
		Map<String, List<Map<String, Object>>> tongge = new HashMap<String, List<Map<String, Object>>>();
		Map<String, BigDecimal> totalrate = new HashMap<String, BigDecimal>();
		Map<String, BigDecimal> totalpayAmount = new HashMap<String, BigDecimal>();
		Map<String, BigDecimal> totaltotalAmount = new HashMap<String, BigDecimal>();
		List<Map<String, Object>> tempValues = new ArrayList<Map<String, Object>>();
		try {

			tempValues = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("list");

		} catch (Exception e) {
			// TODO: handle exception
		}
		titles.add("月份");
		titles.add("收费类目");
		titles.add("应收金额（元）");
		titles.add("已收金额（元）");
		titles.add("未收金额（元）");
		titles.add("收费率");
		titles.add("合计收费率");

		for (Map<String, Object> map : valuesDbase) {

			String date = (String) map.get("fee_date");
			BigDecimal payAmount = (BigDecimal) map.get("payAmount");
			BigDecimal totalAmount = (BigDecimal) map.get("totalAmount");
			if (tongge.get(date) == null) {
				List<Map<String, Object>> tempp = new ArrayList<Map<String, Object>>();

				tempp.add(map);
				tongge.put(date, tempp);
			} else {
				tongge.get(date).add(map);
			}
			if (totalrate.get(date) == null) {
				BigDecimal rate = payAmount.divide(totalAmount, 2,
						BigDecimal.ROUND_HALF_UP);

				totalrate.put(date, rate);

				totalpayAmount.put(date, payAmount);
				totaltotalAmount.put(date, totalAmount);
			} else {
				BigDecimal rate = totalpayAmount
						.get(date)
						.add(payAmount)
						.divide(totaltotalAmount.get(date).add(totalAmount), 2,
								BigDecimal.ROUND_HALF_UP);
				totalpayAmount.put(date, totalpayAmount.get(date)
						.add(payAmount));
				totaltotalAmount.put(date,
						totaltotalAmount.get(date).add(totalAmount));
				totalrate.put(date, rate);
			}

		}

		Map<String, Object> totalmap = tempValues.get(0);
		BigDecimal payAmount = (BigDecimal) totalmap.get("payAmount");
		BigDecimal oweAmount = (BigDecimal) totalmap.get("oweAmount");
		BigDecimal totalAmount = (BigDecimal) totalmap.get("totalAmount");
		BigDecimal rate = payAmount.divide(totalAmount, 2,
				BigDecimal.ROUND_HALF_UP);
		totals.add(totalAmount.toString());
		totals.add(payAmount.toString());
		totals.add(oweAmount.toString());
		totals.add("%" + rate.multiply(BigDecimal.valueOf(100)).toString());
		// =====================获取统计时间
		String stt = st.getStartTime();
		String end = st.getEndTime();

		if (StringUtils.isBlank(stt)) {

			stt = StringUtils.toString(totalmap.get("mindate"), "");

		}

		if (StringUtils.isBlank(end)) {

			end = StringUtils.toString(totalmap.get("maxdate"), "");

		}

		String time = stt.replace("-", ".") + "-" + end.replace("-", ".");

		// =====================获取统计时间
		String fileName = this.processFileName(servletRequest,
				"收费率统计表（单小区-按月份划分）");
		servletResponse.setContentType("application/vnd.ms-excel");
		servletResponse.setHeader("Content-disposition", "attachment;filename="
				+ fileName + ".xls");
		OutputStream ouputStream = servletResponse.getOutputStream();

		String path = servletRequest.getSession().getServletContext()
				.getRealPath("excel");
		System.out.println(path);
		CreateExcellByPoi.outRateTablesSingleCellByMonth(titles, tongge,
				totals, totalrate,
				StringUtils.toString(totalmap.get("comm"), ""), time,
				StringUtils.toString(webServiceSession.getRealname(), ""), path
						+ "/收费率统计表（单小区-按月份划分）1.xls", ouputStream, 2, 7);

		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Response downLoadScheduleOfFeesDetails(
			HttpServletRequest servletRequest,
			HttpServletResponse servletResponse, StatisticsDto st)
			throws Exception {
		String tk = servletRequest.getParameter("tk");
		WebServiceSession webServiceSession = this.getWebServiceSession(tk);
		if (webServiceSession == null) {
			return this.abnormalResp(CodeConst.SYS_CODE_2001,
					InfoConst.SYS_INFO_2001);

		}

		List<Map<String, Object>> valuesDbase = statisticsService
				.paybillDetail(st);
		Map<String, Object> TotalAndTitlesDbase = statisticsService
				.paybyMonAll(st);
		List<String> titles = new ArrayList<String>();
		List<String> keys = new ArrayList<String>();
		List<List<String>> values = new ArrayList<List<String>>();
		List<String> totals = new ArrayList<String>();
		List<Map<String, Object>> tempheaders = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> temptotals = new ArrayList<Map<String, Object>>();
		try {
			tempheaders = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("header");
			temptotals = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("list");

		} catch (Exception e) {
			// TODO: handle exception
		}

		titles.add("序号");
		titles.add("缴费时间");
		titles.add("是否开票");
		titles.add("发票单号");
		titles.add("产权类型");
		titles.add("户号");
		titles.add("业主姓名");
		titles.add("支付方式");
		Map<String, Object> map2 = temptotals.get(0);
		for (Map<String, Object> map1 : tempheaders) {
			String header = (String) map1.get("name");
			String key = (String) map1.get("code");
			BigDecimal total = null;
			if (map2.get("XM" + key) != null) {

				total = (BigDecimal) map2.get("XM" + key);
			} else {
				total = BigDecimal.ZERO;
			}

			titles.add(header);
			totals.add(total.toString());
			keys.add(key);

		}
		titles.add("合计（元）");
		String stt = st.getStartTime();
		String end = st.getEndTime();

		if (StringUtils.isBlank(stt)) {

			stt = StringUtils.toString(map2.get("mindate"), "");

		}

		if (StringUtils.isBlank(end)) {

			end = StringUtils.toString(map2.get("maxdate"), "");

		}

		String time = stt.replace("-", ".") + "-" + end.replace("-", ".");
		BigDecimal totaltotal = (BigDecimal) map2.get("totalAmount");
		totals.add("" + totaltotal);

		int k = 1;
		for (Map<String, Object> map : valuesDbase) {

			List<String> rowValue = new ArrayList<String>();

			rowValue.add("" + k);
			k++;
			String paytime = StringUtils.toString(map.get("pay_time"));
			String isinvoice = (String) map.get("isinvoice");
			String apartmentType = (String) map.get("apartmentType");
			String invoice_num = (String) map.get("invoice_num");
			String building_name = (String) map.get("building_name");
			String unit_name = (String) map.get("unit_name");
			String apartment_num = (String) map.get("apartment_num");
			String ownerName = (String) map.get("ownerName");
			String pay_type = (String) map.get("pay_type");
			rowValue.add(paytime);
			rowValue.add(isinvoice);
			rowValue.add(invoice_num);
			rowValue.add(apartmentType);

			rowValue.add(building_name + "_" + unit_name + "_" + apartment_num);
			rowValue.add(ownerName);
			rowValue.add(pay_type);

			for (int i = 0; i < keys.size(); i++) {

				BigDecimal value = (BigDecimal) map.get("XM" + (keys.get(i)));

				rowValue.add(value.toString());

			}

			BigDecimal totalvalue = (BigDecimal) map.get("pay_money");
			rowValue.add(totalvalue.toString());

			values.add(rowValue);
		}

		String fileName = this.processFileName(servletRequest, "缴费明细表（月度明细）");
		servletResponse.setContentType("application/vnd.ms-excel");
		servletResponse.setHeader("Content-disposition", "attachment;filename="
				+ fileName + ".xls");
		OutputStream ouputStream = servletResponse.getOutputStream();
		String path = servletRequest.getSession().getServletContext()
				.getRealPath("excel");
		CreateExcellByPoi.outScheduleOfFeesByMonth(titles, values, totals,
				StringUtils.toString(map2.get("comm"), ""), time,
				StringUtils.toString(webServiceSession.getRealname(), ""), path
						+ "/缴费明细表（月度明细）.xls", ouputStream, 8, 14);

		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Response downTuitionTablesBySingleCellAndSpiltMonth(
			HttpServletRequest servletRequest,
			HttpServletResponse servletResponse, StatisticsDto st)
			throws Exception {

		String tk = servletRequest.getParameter("tk");
		WebServiceSession webServiceSession = this.getWebServiceSession(tk);
		if (webServiceSession == null) {

			return this.abnormalResp(CodeConst.SYS_CODE_2001,
					InfoConst.SYS_INFO_2001);

		}

		List<Map<String, Object>> valuesDbase = statisticsService
				.getOwerOweFeeInfo(st, "2");

		Map<String, Object> TotalAndTitlesDbase = statisticsService
				.getOwerOweFeeInfoAll(st, null);

		Map<String, Map<String, BigDecimal>> forTotal = new HashMap<String, Map<String, BigDecimal>>();
		Map<String, BigDecimal> RowTotal = new HashMap<String, BigDecimal>();
		List<String> titles = new ArrayList<String>();
		List<String> secondtitles = new ArrayList<String>();
		List<String> monthtitles = new ArrayList<String>();
		List<String> keys = new ArrayList<String>();
		List<List<String>> values = new ArrayList<List<String>>();
		List<String> totals = new ArrayList<String>();
		List<String> bigtotals = new ArrayList<String>();
		List<Map<String, Object>> tempheaders = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> temptotals = new ArrayList<Map<String, Object>>();
		try {
			tempheaders = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("header");
			temptotals = (List<Map<String, Object>>) TotalAndTitlesDbase
					.get("list");

		} catch (Exception e) {
			// TODO: handle exception
		}
		Map<String, Object> map2 = temptotals.get(0);

		String stt = st.getStartTime();
		String end1 = st.getEndTime();

		if (StringUtils.isBlank(stt)) {

			stt = StringUtils.toString(map2.get("mindate"), "");

		}

		if (StringUtils.isBlank(end1)) {

			end1 = StringUtils.toString(map2.get("maxdate"), "");

		}

		String time = stt.replace("-", ".") + "-" + end1.replace("-", ".");

		titles.add("序号");
		titles.add("产权类型");
		titles.add("户号");
		titles.add("业主姓名");
		titles.add("计费面积");

		for (Map<String, Object> map1 : tempheaders) {
			String header = (String) map1.get("name");
			String key = (String) map1.get("code");
			secondtitles.add(header);
			keys.add(key);

		}

		Set<String> monthSet = new TreeSet<String>();

		for (Map<String, Object> map : valuesDbase) {

			String fee_date = (String) map.get("fee_date");
			// System.out.println(fee_date);
			monthSet.add(fee_date);

		}
		for (String string : monthSet) {
			monthtitles.add(string);
		}

		Map<String, Integer> weizi = new HashMap<String, Integer>();
		Map<Integer, String> daoweizi = new HashMap<Integer, String>();

		int n = 0;
		int k = 1;
		for (Map<String, Object> map : valuesDbase) {
			int p = 1;
			String date = map.get("fee_date").toString();

			Iterator<String> it = monthSet.iterator();

			for (int i = 0; i < monthSet.size(); i++) {
				String temp = it.next();
				if (temp.equals(date)) {
					p = i + p;
				}
			}

			List<String> rowValue = new ArrayList<String>();

			if (weizi.get(map.get("apartment_id").toString()) == null) {

				String temp = (String) map.get("apartmentType");
				String temp1 = (String) map.get("building_name");
				String temp2 = (String) map.get("unit_name");
				String temp3 = (String) map.get("apartment_num");
				String temp4 = (String) map.get("ownerName");
				String temp5 = (String) map.get("fee_area");
				rowValue.add(k + "");
				k++;
				rowValue.add(temp);
				rowValue.add(temp1 + "_" + temp2 + "_" + temp3);
				rowValue.add(temp4);
				rowValue.add(temp5);

				int start = p * secondtitles.size();

				for (int i = titles.size(); i < start; i++) {
					rowValue.add("0.0");

				}

				for (int j = 0; j < keys.size(); j++) {

					BigDecimal value = (BigDecimal) map.get("XM" + keys.get(j));
					Map<String, BigDecimal> tempmap = null;
					if (forTotal.get(map.get("fee_date").toString()) == null) {
						tempmap = new HashMap<String, BigDecimal>();
						tempmap.put("XM" + keys.get(j), value);
						forTotal.put(map.get("fee_date").toString(), tempmap);

					} else {
						tempmap = forTotal.get(map.get("fee_date").toString());
						if (tempmap.get("XM" + keys.get(j)) != null) {
							tempmap.put("XM" + keys.get(j),
									tempmap.get("XM" + keys.get(j)).add(value));
							forTotal.put(map.get("fee_date").toString(),
									tempmap);
						} else {

							tempmap.put("XM" + keys.get(j), value);
							forTotal.put(map.get("fee_date").toString(),
									tempmap);
						}

					}

					rowValue.add(value.toString());

				}

				BigDecimal temt = (BigDecimal) map.get("totalAmount");
				if (RowTotal.get(map.get("apartment_id").toString()) == null) {
					RowTotal.put(map.get("apartment_id").toString(), temt);
				} else {
					BigDecimal temx = RowTotal.get(map.get("apartment_id")
							.toString());
					RowTotal.put(map.get("apartment_id").toString(),
							temx.add(temt));
				}

				weizi.put(map.get("apartment_id").toString(), n);
				daoweizi.put(n, map.get("apartment_id").toString());
				n++;
				values.add(rowValue);

			} else {
				rowValue = values.get(weizi.get(map.get("apartment_id")
						.toString()));

				for (int j = 0; j < keys.size(); j++) {

					BigDecimal value = (BigDecimal) map.get("XM" + keys.get(j));
					Map<String, BigDecimal> tempmap = null;
					if (forTotal.get(map.get("fee_date").toString()) == null) {
						tempmap = new HashMap<String, BigDecimal>();
						tempmap.put("XM" + keys.get(j), value);
						forTotal.put(map.get("fee_date").toString(), tempmap);

					} else {
						tempmap = forTotal.get(map.get("fee_date").toString());
						if (tempmap.get("XM" + keys.get(j)) != null) {
							tempmap.put("XM" + keys.get(j),
									tempmap.get("XM" + keys.get(j)).add(value));
							forTotal.put(map.get("fee_date").toString(),
									tempmap);
						} else {

							tempmap.put("XM" + keys.get(j), value);
							forTotal.put(map.get("fee_date").toString(),
									tempmap);
						}

					}

					rowValue.add(value.toString());

				}

				BigDecimal temt = (BigDecimal) map.get("totalAmount");
				if (RowTotal.get(map.get("apartment_id").toString()) == null) {
					RowTotal.put(map.get("apartment_id").toString(), temt);
				} else {
					BigDecimal temx = RowTotal.get(map.get("apartment_id")
							.toString());
					RowTotal.put(map.get("apartment_id").toString(),
							temx.add(temt));
				}

				values.set(weizi.get(map.get("apartment_id").toString()),
						rowValue);

			}

		}
		int end = monthSet.size() * 5 + 5;

		for (int i = 0; i < values.size(); i++) {

			List<String> temp = values.get(i);
			String apartment_id = daoweizi.get(i);
			for (int j = temp.size(); j < end; j++) {
				temp.add("0.0");

			}

			temp.add(RowTotal.get(apartment_id).toString());

		}

		TreeSet<String> totalKeySet = new TreeSet<String>(forTotal.keySet());
		for (String Mapkeys : totalKeySet) {

			BigDecimal forbigtotal = BigDecimal.ZERO;
			Map<String, BigDecimal> smap = forTotal.get(Mapkeys);
			for (int i = 0; i < keys.size(); i++) {

				BigDecimal value = (BigDecimal) smap.get("XM" + keys.get(i));

				totals.add(value.toString());
				forbigtotal = forbigtotal.add(value);

			}

			bigtotals.add(forbigtotal.toString());

		}
		BigDecimal totaltotal = null;
		if (map2.get("totalAmount") != null) {

			totaltotal = (BigDecimal) map2.get("totalAmount");
		} else {
			totaltotal = BigDecimal.ZERO;
		}
		totals.add(totaltotal.toString());

		String fileName = this.processFileName(servletRequest,
				"欠费统计表（单小区明细-拆分月份显示）");
		servletResponse.setContentType("application/vnd.ms-excel");
		servletResponse.setHeader("Content-disposition", "attachment;filename="
				+ fileName + ".xlsx");
		OutputStream ouputStream = servletResponse.getOutputStream();

		String path = servletRequest.getSession().getServletContext()
				.getRealPath("excel");
		System.out.println(path);
		CreateExcellByPoi.outTuitionTablesBySingleCellAndSplitMonth(titles,
				monthtitles, secondtitles, values, totals, bigtotals,
				totaltotal.toString(),
				StringUtils.toString(map2.get("comm"), ""), time,
				StringUtils.toString(webServiceSession.getRealname(), ""), path
						+ "/欠费统计表（单小区明细-拆分月份显示）1.xlsx", ouputStream, 5, 21);

		return null;
	}

	@Override
	public Response paybillDetail(HttpServletRequest servletRequest,
			StatisticsDto st) throws Exception {

		List<Map<String, Object>> l = statisticsService.paybillDetail(st);
		return this.normalResp(0, 1, 0l, l);

	}

	public String processFileName(HttpServletRequest request, String fileNames) {
		String codedfilename = null;
		try {
			String agent = request.getHeader("USER-AGENT");
			if (null != agent && -1 != agent.indexOf("MSIE") || null != agent
					&& -1 != agent.indexOf("Trident")) {// ie

				String name = java.net.URLEncoder.encode(fileNames, "UTF8");

				codedfilename = name;
			} else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,chrome等

				codedfilename = new String(fileNames.getBytes("UTF-8"),
						"iso-8859-1");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return codedfilename;
	}

	@Override
	public Response getMonthPay(HttpServletRequest servletRequest,
			String commID, String apartmentNum, String apartmentType,
			String startDate, String endDate, String pageIndex, String pageSize)
			throws Exception {
		int pageNum = 1;
		int pages = 200;
		if (!StringUtils.isBlank(pageIndex)) {
			if (!ValidateUtil.isNumber(pageIndex)) {
				return this.abnormalResp(CodeConst.SYS_CODE_1003,
						InfoConst.SYS_INFO_1003, "当前页数", pageIndex);

			}
		}

		/* 每页总条数不为空的场合 */
		if (!StringUtils.isBlank(pageSize)) {
			if (!ValidateUtil.isNumber(pageSize)) {
				logger.error(InfoConst.SYS_INFO_1003, "每页总条数", pageSize);
				return this.abnormalResp(CodeConst.SYS_CODE_1003,
						InfoConst.SYS_INFO_1003, "每页总条数", pageSize);

			}
		}

		/* 当前页数值设定 */
		if (StringUtils.isBlank(pageIndex)) {

		} else {
			pageNum = Integer.parseInt(pageIndex);
		}

		/* 每页总条数设定 */
		if (StringUtils.isBlank(pageSize)) {

		} else {
			pages = Integer.parseInt(pageSize);
		}

		List<Map<String, Object>> l = statisticsService
				.getMonthPay(commID, apartmentNum, apartmentType, startDate,
						endDate, pageNum, pages);
		Long total = ((Page<Map<String, Object>>) l).getTotal();

		for (Map<String, Object> map : l) {

			if (map.get("maxDate") != null && map.get("apartment_id") != null) {
				if (!StringUtils.isBlank(map.get("maxDate").toString())
						&& !StringUtils.isBlank(map.get("apartment_id")
								.toString())) {
					Map<String, Object> de = statisticsService.findDetail(map
							.get("apartment_id").toString(), map.get("maxDate")
							.toString());
					if (de != null && de.size() > 0) {
						map.put("stotalMoney",
								de.get("feeTotal") != null ? de.get("feeTotal")
										: "0");
					}
				}
			}

		}

		return this.normalResp(pageNum, pages, total,
				PropertyUtils.turnToEntity(l));

	}
}
